{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 高中体侧数据转换"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## 数据加载，pd.read_excel('./18级高一体测成绩汇总.xls')默认加载第一个工作表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')#忽略警告"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "score_male = pd.read_excel(r'.\\18级高一体测成绩汇总.xls')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## 数据加载， pd.read_excel('./18级高一体测成绩汇总.xls',sheet_name = 1)指定加载第二个工作表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "score_female = pd.read_excel(r'.\\18级高一体测成绩汇总.xls',sheet_name='女')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "##  评分标准加载，pd.read_excel('./体侧成绩评分表.xls',header = [0,1])，header=[0,1]表示多层列索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "score_rule = pd.read_excel(r'.\\体侧成绩评分表.xls',header=[0,1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "##  数据类型转换"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "###  男1000米跑，数据类型是str，并且是4’26这种形式，需要变成float类型的值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      4.13\n",
       "1      4.16\n",
       "2      4.09\n",
       "3      4.21\n",
       "4      3.44\n",
       "       ... \n",
       "472    4.23\n",
       "473    5.19\n",
       "474    3.25\n",
       "475    4.39\n",
       "476    0.00\n",
       "Name: 男1000米跑, Length: 477, dtype: float64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def clean_score(score):\n",
    "    if isinstance(score,str):\n",
    "        score = score.split(\"'\")\n",
    "        return float(score[0])+float(score[1])/100\n",
    "    else:\n",
    "        return score\n",
    "score_male[\"男1000米跑\"] = score_male.男1000米跑.map(clean_score)\n",
    "score_male[\"男1000米跑\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "###  评分标准中男1000米跑和女800米跑的成绩都是4‘10’‘这种形式，需要转化为float类型值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     3.30\n",
       "1     3.35\n",
       "2     3.40\n",
       "3     3.47\n",
       "4     3.55\n",
       "5     4.00\n",
       "6     4.05\n",
       "7     4.10\n",
       "8     4.15\n",
       "9     4.20\n",
       "10    4.25\n",
       "11    4.30\n",
       "12    4.35\n",
       "13    4.40\n",
       "14    4.45\n",
       "15    5.05\n",
       "16    5.25\n",
       "17    5.45\n",
       "18    6.05\n",
       "19    6.25\n",
       "Name: 成绩, dtype: float64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def clean_scorerule(score):\n",
    "    m,s = [int(i) for i in score.strip('\"').split(\"'\")]\n",
    "    return m + s/100.0\n",
    "score_rule.iloc[:,-4] = score_rule.iloc[:,-4].transform(clean_scorerule)\n",
    "score_rule.iloc[:,-2] = score_rule.iloc[:,-2].transform(clean_scorerule)\n",
    "score_rule[\"男1000米跑\"][\"成绩\"] \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "###  其他所有数值类型的值，都要转换为float类型的值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "score_male[\"男50米跑\"] = score_male[\"男50米跑\"].map(lambda x:float(x))\n",
    "score_male[\"男跳远\"] = score_male[\"男跳远\"].map(lambda x:float(x))\n",
    "score_male[\"男体前屈\"] = score_male[\"男体前屈\"].map(lambda x:float(x))\n",
    "score_male[\"男引体\"] = score_male[\"男引体\"].map(lambda x:float(x))\n",
    "score_male[\"男肺活量\"] = score_male[\"男肺活量\"].map(lambda x:float(x))\n",
    "score_male[\"身高\"] = score_male[\"身高\"].map(lambda x:float(x))\n",
    "score_male[\"体重\"] = score_male[\"体重\"].map(lambda x:float(x))\n",
    "score_male[\"BMI\"] = score_male[\"BMI\"].map(lambda x:float(x))\n",
    "score_female[\"女50米跑\"] = score_female[\"女50米跑\"].map(lambda x:float(x))\n",
    "score_female[\"女跳远\"] = score_female[\"女跳远\"].map(lambda x:float(x))\n",
    "score_female[\"女体前屈\"] = score_female[\"女体前屈\"].map(lambda x:float(x))\n",
    "score_female[\"女仰卧\"] = score_female[\"女仰卧\"].map(lambda x:float(x))\n",
    "score_female[\"女肺活量\"] = score_female[\"女肺活量\"].map(lambda x:float(x))\n",
    "score_female[\"身高\"] = score_female[\"身高\"].map(lambda x:float(x))\n",
    "score_female[\"体重\"] = score_female[\"体重\"].map(lambda x:float(x))\n",
    "score_female[\"BMI\"] = score_female[\"BMI\"].map(lambda x:float(x))\n",
    "score_rule[\"BMI\"] = score_female[\"BMI\"].map(lambda x:float(x))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "##  对体侧成绩进行分数转换，跑步类（越小越好）；跳远、体前屈（越大越好）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "###  男生"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "####  跑步类"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "      <th>男50米跑分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>72</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>74</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>68</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>85</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.23</td>\n",
       "      <td>8.27</td>\n",
       "      <td>208.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4647.0</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>68</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>5.19</td>\n",
       "      <td>9.55</td>\n",
       "      <td>210.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7042.0</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3.25</td>\n",
       "      <td>7.50</td>\n",
       "      <td>252.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>5755.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.39</td>\n",
       "      <td>7.81</td>\n",
       "      <td>208.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>5688.0</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  男1000米跑  男50米跑    男跳远  男体前屈   男引体    男肺活量     身高    体重  BMI  \\\n",
       "0     1  男     4.13   8.88  195.0  12.0   1.0  2785.0  170.0  72.6  0.0   \n",
       "1     1  男     4.16   7.70  225.0  11.0   7.0  3133.0  174.0  52.7  0.0   \n",
       "2     1  男     4.09   8.45  218.0  14.0   1.0  3901.0  169.0  46.5  0.0   \n",
       "3     1  男     4.21   8.05  206.0  13.0   1.0  4946.0  183.0  79.7  0.0   \n",
       "4     1  男     3.44   7.52  210.0  13.0   9.0  3538.0  171.0  54.7  0.0   \n",
       "..   .. ..      ...    ...    ...   ...   ...     ...    ...   ...  ...   \n",
       "472  17  男     4.23   8.27  208.0  10.0   0.0  4647.0  176.0  69.5  0.0   \n",
       "473  17  男     5.19   9.55  210.0  15.0   6.0  7042.0  177.0  76.0  0.0   \n",
       "474  17  男     3.25   7.50  252.0  13.0  13.0  5755.0  181.0  65.0  0.0   \n",
       "475  17  男     4.39   7.81  208.0  14.0  11.0  5688.0  172.0  51.7  0.0   \n",
       "476  17  男     0.00   0.00    0.0   0.0   0.0     0.0    0.0   0.0  0.0   \n",
       "\n",
       "     男1000米跑分数  男50米跑分数  \n",
       "0           72       66  \n",
       "1           70       78  \n",
       "2           74       70  \n",
       "3           68       74  \n",
       "4           85       78  \n",
       "..         ...      ...  \n",
       "472         68       72  \n",
       "473         40       50  \n",
       "474        100       80  \n",
       "475         62       76  \n",
       "476          0        0  \n",
       "\n",
       "[477 rows x 13 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#输入成绩，返回分数\n",
    "cols = [\"男1000米跑\",\"男50米跑\"]\n",
    "for col in cols:\n",
    "    def getScore(result):\n",
    "        if result == 0: #未参加测试\n",
    "            return 0\n",
    "        for i in range(score_rule[col].shape[0]):#遍历评分表中的成绩\n",
    "            if result <= score_rule[col][\"成绩\"][i]:\n",
    "                return score_rule[col][\"分数\"][i]\n",
    "        return 0\n",
    "    score_male[col + \"分数\"] = score_male[col].map(getScore)\n",
    "score_male\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "####  非跑步类"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "      <th>男50米跑分数</th>\n",
       "      <th>男跳远分数</th>\n",
       "      <th>男体前屈分数</th>\n",
       "      <th>男引体分数</th>\n",
       "      <th>男肺活量分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>72</td>\n",
       "      <td>66</td>\n",
       "      <td>60</td>\n",
       "      <td>74</td>\n",
       "      <td>0</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70</td>\n",
       "      <td>78</td>\n",
       "      <td>74</td>\n",
       "      <td>74</td>\n",
       "      <td>60</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>74</td>\n",
       "      <td>70</td>\n",
       "      <td>70</td>\n",
       "      <td>78</td>\n",
       "      <td>0</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>68</td>\n",
       "      <td>74</td>\n",
       "      <td>64</td>\n",
       "      <td>76</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>85</td>\n",
       "      <td>78</td>\n",
       "      <td>66</td>\n",
       "      <td>76</td>\n",
       "      <td>68</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.23</td>\n",
       "      <td>8.27</td>\n",
       "      <td>208.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4647.0</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>68</td>\n",
       "      <td>72</td>\n",
       "      <td>66</td>\n",
       "      <td>72</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>5.19</td>\n",
       "      <td>9.55</td>\n",
       "      <td>210.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7042.0</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40</td>\n",
       "      <td>50</td>\n",
       "      <td>66</td>\n",
       "      <td>80</td>\n",
       "      <td>50</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3.25</td>\n",
       "      <td>7.50</td>\n",
       "      <td>252.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>5755.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100</td>\n",
       "      <td>80</td>\n",
       "      <td>90</td>\n",
       "      <td>76</td>\n",
       "      <td>85</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.39</td>\n",
       "      <td>7.81</td>\n",
       "      <td>208.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>5688.0</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62</td>\n",
       "      <td>76</td>\n",
       "      <td>66</td>\n",
       "      <td>78</td>\n",
       "      <td>76</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  男1000米跑  男50米跑    男跳远  男体前屈   男引体    男肺活量     身高    体重  BMI  \\\n",
       "0     1  男     4.13   8.88  195.0  12.0   1.0  2785.0  170.0  72.6  0.0   \n",
       "1     1  男     4.16   7.70  225.0  11.0   7.0  3133.0  174.0  52.7  0.0   \n",
       "2     1  男     4.09   8.45  218.0  14.0   1.0  3901.0  169.0  46.5  0.0   \n",
       "3     1  男     4.21   8.05  206.0  13.0   1.0  4946.0  183.0  79.7  0.0   \n",
       "4     1  男     3.44   7.52  210.0  13.0   9.0  3538.0  171.0  54.7  0.0   \n",
       "..   .. ..      ...    ...    ...   ...   ...     ...    ...   ...  ...   \n",
       "472  17  男     4.23   8.27  208.0  10.0   0.0  4647.0  176.0  69.5  0.0   \n",
       "473  17  男     5.19   9.55  210.0  15.0   6.0  7042.0  177.0  76.0  0.0   \n",
       "474  17  男     3.25   7.50  252.0  13.0  13.0  5755.0  181.0  65.0  0.0   \n",
       "475  17  男     4.39   7.81  208.0  14.0  11.0  5688.0  172.0  51.7  0.0   \n",
       "476  17  男     0.00   0.00    0.0   0.0   0.0     0.0    0.0   0.0  0.0   \n",
       "\n",
       "     男1000米跑分数  男50米跑分数  男跳远分数  男体前屈分数  男引体分数  男肺活量分数  \n",
       "0           72       66     60      74      0      62  \n",
       "1           70       78     74      74     60      68  \n",
       "2           74       70     70      78      0      80  \n",
       "3           68       74     64      76      0     100  \n",
       "4           85       78     66      76     68      74  \n",
       "..         ...      ...    ...     ...    ...     ...  \n",
       "472         68       72     66      72      0     100  \n",
       "473         40       50     66      80     50     100  \n",
       "474        100       80     90      76     85     100  \n",
       "475         62       76     66      78     76     100  \n",
       "476          0        0      0       0      0       0  \n",
       "\n",
       "[477 rows x 17 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#输入成绩，返回分数\n",
    "cols = [\"男跳远\",\"男体前屈\",\"男引体\",\"男肺活量\"]\n",
    "for col in cols:\n",
    "    def getScore(result):\n",
    "        if result == 0: #未参加测试\n",
    "            return 0\n",
    "        for i in range(score_rule[col].shape[0]):#遍历评分表中的成绩\n",
    "            if result >= score_rule[col][\"成绩\"][i]:\n",
    "                return score_rule[col][\"分数\"][i]\n",
    "        return 0\n",
    "    score_male[col + \"分数\"] = score_male[col].map(getScore)\n",
    "score_male"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "####  计算BMI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "      <th>男50米跑分数</th>\n",
       "      <th>男跳远分数</th>\n",
       "      <th>男体前屈分数</th>\n",
       "      <th>男引体分数</th>\n",
       "      <th>男肺活量分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>25.12</td>\n",
       "      <td>72</td>\n",
       "      <td>66</td>\n",
       "      <td>60</td>\n",
       "      <td>74</td>\n",
       "      <td>0</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>17.41</td>\n",
       "      <td>70</td>\n",
       "      <td>78</td>\n",
       "      <td>74</td>\n",
       "      <td>74</td>\n",
       "      <td>60</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>16.28</td>\n",
       "      <td>74</td>\n",
       "      <td>70</td>\n",
       "      <td>70</td>\n",
       "      <td>78</td>\n",
       "      <td>0</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>23.80</td>\n",
       "      <td>68</td>\n",
       "      <td>74</td>\n",
       "      <td>64</td>\n",
       "      <td>76</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>18.71</td>\n",
       "      <td>85</td>\n",
       "      <td>78</td>\n",
       "      <td>66</td>\n",
       "      <td>76</td>\n",
       "      <td>68</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.23</td>\n",
       "      <td>8.27</td>\n",
       "      <td>208.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4647.0</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.5</td>\n",
       "      <td>22.44</td>\n",
       "      <td>68</td>\n",
       "      <td>72</td>\n",
       "      <td>66</td>\n",
       "      <td>72</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>5.19</td>\n",
       "      <td>9.55</td>\n",
       "      <td>210.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7042.0</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>24.26</td>\n",
       "      <td>40</td>\n",
       "      <td>50</td>\n",
       "      <td>66</td>\n",
       "      <td>80</td>\n",
       "      <td>50</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3.25</td>\n",
       "      <td>7.50</td>\n",
       "      <td>252.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>5755.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>19.84</td>\n",
       "      <td>100</td>\n",
       "      <td>80</td>\n",
       "      <td>90</td>\n",
       "      <td>76</td>\n",
       "      <td>85</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.39</td>\n",
       "      <td>7.81</td>\n",
       "      <td>208.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>5688.0</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>17.48</td>\n",
       "      <td>62</td>\n",
       "      <td>76</td>\n",
       "      <td>66</td>\n",
       "      <td>78</td>\n",
       "      <td>76</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  男1000米跑  男50米跑    男跳远  男体前屈   男引体    男肺活量     身高    体重    BMI  \\\n",
       "0     1  男     4.13   8.88  195.0  12.0   1.0  2785.0  170.0  72.6  25.12   \n",
       "1     1  男     4.16   7.70  225.0  11.0   7.0  3133.0  174.0  52.7  17.41   \n",
       "2     1  男     4.09   8.45  218.0  14.0   1.0  3901.0  169.0  46.5  16.28   \n",
       "3     1  男     4.21   8.05  206.0  13.0   1.0  4946.0  183.0  79.7  23.80   \n",
       "4     1  男     3.44   7.52  210.0  13.0   9.0  3538.0  171.0  54.7  18.71   \n",
       "..   .. ..      ...    ...    ...   ...   ...     ...    ...   ...    ...   \n",
       "472  17  男     4.23   8.27  208.0  10.0   0.0  4647.0  176.0  69.5  22.44   \n",
       "473  17  男     5.19   9.55  210.0  15.0   6.0  7042.0  177.0  76.0  24.26   \n",
       "474  17  男     3.25   7.50  252.0  13.0  13.0  5755.0  181.0  65.0  19.84   \n",
       "475  17  男     4.39   7.81  208.0  14.0  11.0  5688.0  172.0  51.7  17.48   \n",
       "476  17  男     0.00   0.00    0.0   0.0   0.0     0.0    0.0   0.0    NaN   \n",
       "\n",
       "     男1000米跑分数  男50米跑分数  男跳远分数  男体前屈分数  男引体分数  男肺活量分数  \n",
       "0           72       66     60      74      0      62  \n",
       "1           70       78     74      74     60      68  \n",
       "2           74       70     70      78      0      80  \n",
       "3           68       74     64      76      0     100  \n",
       "4           85       78     66      76     68      74  \n",
       "..         ...      ...    ...     ...    ...     ...  \n",
       "472         68       72     66      72      0     100  \n",
       "473         40       50     66      80     50     100  \n",
       "474        100       80     90      76     85     100  \n",
       "475         62       76     66      78     76     100  \n",
       "476          0        0      0       0      0       0  \n",
       "\n",
       "[477 rows x 17 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "score_male[\"BMI\"] = (score_male[\"体重\"]/(score_male[\"身高\"]/100)/(score_male[\"身高\"]/100)).round(2)\n",
    "score_male"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "####  重排列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男50米跑分数</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男跳远分数</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男体前屈分数</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男引体分数</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>男肺活量分数</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>72</td>\n",
       "      <td>8.88</td>\n",
       "      <td>66</td>\n",
       "      <td>195.0</td>\n",
       "      <td>60</td>\n",
       "      <td>12.0</td>\n",
       "      <td>74</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>62</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>25.121107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>70</td>\n",
       "      <td>7.70</td>\n",
       "      <td>78</td>\n",
       "      <td>225.0</td>\n",
       "      <td>74</td>\n",
       "      <td>11.0</td>\n",
       "      <td>74</td>\n",
       "      <td>7.0</td>\n",
       "      <td>60</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>68</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>17.406527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>74</td>\n",
       "      <td>8.45</td>\n",
       "      <td>70</td>\n",
       "      <td>218.0</td>\n",
       "      <td>70</td>\n",
       "      <td>14.0</td>\n",
       "      <td>78</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>80</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>16.280943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>68</td>\n",
       "      <td>8.05</td>\n",
       "      <td>74</td>\n",
       "      <td>206.0</td>\n",
       "      <td>64</td>\n",
       "      <td>13.0</td>\n",
       "      <td>76</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>100</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>23.798859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>85</td>\n",
       "      <td>7.52</td>\n",
       "      <td>78</td>\n",
       "      <td>210.0</td>\n",
       "      <td>66</td>\n",
       "      <td>13.0</td>\n",
       "      <td>76</td>\n",
       "      <td>9.0</td>\n",
       "      <td>68</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>74</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>18.706611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.23</td>\n",
       "      <td>68</td>\n",
       "      <td>8.27</td>\n",
       "      <td>72</td>\n",
       "      <td>208.0</td>\n",
       "      <td>66</td>\n",
       "      <td>10.0</td>\n",
       "      <td>72</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>4647.0</td>\n",
       "      <td>100</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.5</td>\n",
       "      <td>22.436725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>5.19</td>\n",
       "      <td>40</td>\n",
       "      <td>9.55</td>\n",
       "      <td>50</td>\n",
       "      <td>210.0</td>\n",
       "      <td>66</td>\n",
       "      <td>15.0</td>\n",
       "      <td>80</td>\n",
       "      <td>6.0</td>\n",
       "      <td>50</td>\n",
       "      <td>7042.0</td>\n",
       "      <td>100</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>24.258674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3.25</td>\n",
       "      <td>100</td>\n",
       "      <td>7.50</td>\n",
       "      <td>80</td>\n",
       "      <td>252.0</td>\n",
       "      <td>90</td>\n",
       "      <td>13.0</td>\n",
       "      <td>76</td>\n",
       "      <td>13.0</td>\n",
       "      <td>85</td>\n",
       "      <td>5755.0</td>\n",
       "      <td>100</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>19.840664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.39</td>\n",
       "      <td>62</td>\n",
       "      <td>7.81</td>\n",
       "      <td>76</td>\n",
       "      <td>208.0</td>\n",
       "      <td>66</td>\n",
       "      <td>14.0</td>\n",
       "      <td>78</td>\n",
       "      <td>11.0</td>\n",
       "      <td>76</td>\n",
       "      <td>5688.0</td>\n",
       "      <td>100</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>17.475663</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  男1000米跑  男1000米跑分数  男50米跑  男50米跑分数    男跳远  男跳远分数  男体前屈  男体前屈分数  \\\n",
       "0     1  男     4.13         72   8.88       66  195.0     60  12.0      74   \n",
       "1     1  男     4.16         70   7.70       78  225.0     74  11.0      74   \n",
       "2     1  男     4.09         74   8.45       70  218.0     70  14.0      78   \n",
       "3     1  男     4.21         68   8.05       74  206.0     64  13.0      76   \n",
       "4     1  男     3.44         85   7.52       78  210.0     66  13.0      76   \n",
       "..   .. ..      ...        ...    ...      ...    ...    ...   ...     ...   \n",
       "472  17  男     4.23         68   8.27       72  208.0     66  10.0      72   \n",
       "473  17  男     5.19         40   9.55       50  210.0     66  15.0      80   \n",
       "474  17  男     3.25        100   7.50       80  252.0     90  13.0      76   \n",
       "475  17  男     4.39         62   7.81       76  208.0     66  14.0      78   \n",
       "476  17  男     0.00          0   0.00        0    0.0      0   0.0       0   \n",
       "\n",
       "      男引体  男引体分数    男肺活量  男肺活量分数     身高    体重        BMI  \n",
       "0     1.0      0  2785.0      62  170.0  72.6  25.121107  \n",
       "1     7.0     60  3133.0      68  174.0  52.7  17.406527  \n",
       "2     1.0      0  3901.0      80  169.0  46.5  16.280943  \n",
       "3     1.0      0  4946.0     100  183.0  79.7  23.798859  \n",
       "4     9.0     68  3538.0      74  171.0  54.7  18.706611  \n",
       "..    ...    ...     ...     ...    ...   ...        ...  \n",
       "472   0.0      0  4647.0     100  176.0  69.5  22.436725  \n",
       "473   6.0     50  7042.0     100  177.0  76.0  24.258674  \n",
       "474  13.0     85  5755.0     100  181.0  65.0  19.840664  \n",
       "475  11.0     76  5688.0     100  172.0  51.7  17.475663  \n",
       "476   0.0      0     0.0       0    0.0   0.0        NaN  \n",
       "\n",
       "[477 rows x 17 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = ['班级', '性别', '男1000米跑','男1000米跑分数',  '男50米跑','男50米跑分数', \n",
    "        '男跳远','男跳远分数', '男体前屈','男体前屈分数', '男引体','男引体分数', \n",
    "        '男肺活量', '男肺活量分数','身高',\n",
    "       '体重', 'BMI']\n",
    "score_male[cols]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "###  女生"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "####  跑步类"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>女800米跑分数</th>\n",
       "      <th>女50米跑分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>9.32</td>\n",
       "      <td>185.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>11.44</td>\n",
       "      <td>148.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>13.40</td>\n",
       "      <td>150.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>9.52</td>\n",
       "      <td>172.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>85</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>9.79</td>\n",
       "      <td>145.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>85</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>588</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.51</td>\n",
       "      <td>9.60</td>\n",
       "      <td>150.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>2255.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>78</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>589</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.00</td>\n",
       "      <td>10.18</td>\n",
       "      <td>150.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>2937.0</td>\n",
       "      <td>161.0</td>\n",
       "      <td>55.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>76</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.45</td>\n",
       "      <td>10.18</td>\n",
       "      <td>152.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>2592.0</td>\n",
       "      <td>165.0</td>\n",
       "      <td>48.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>80</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>591</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.01</td>\n",
       "      <td>9.67</td>\n",
       "      <td>165.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>1829.0</td>\n",
       "      <td>154.0</td>\n",
       "      <td>43.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>74</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>592</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.48</td>\n",
       "      <td>9.09</td>\n",
       "      <td>180.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>2962.0</td>\n",
       "      <td>162.0</td>\n",
       "      <td>55.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>593 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  女800米跑  女50米跑    女跳远  女体前屈   女仰卧    女肺活量     身高    体重  BMI  \\\n",
       "0     1  女    3.22   9.32  185.0  16.0  48.0  3775.0  163.0  51.3  0.0   \n",
       "1     1  女    4.59  11.44  148.0   9.0  29.0  3683.0  163.0  66.6  0.0   \n",
       "2     1  女    3.46  13.40  150.0   7.0  40.0  3331.0  157.0  60.0  0.0   \n",
       "3     1  女    3.39   9.52  172.0  21.0  46.0  3701.0  160.0  50.7  0.0   \n",
       "4     1  女    3.43   9.79  145.0   8.0  34.0  3592.0  167.0  63.9  0.0   \n",
       "..   .. ..     ...    ...    ...   ...   ...     ...    ...   ...  ...   \n",
       "588  17  女    3.51   9.60  150.0  24.0  41.0  2255.0  158.0  49.0  0.0   \n",
       "589  17  女    4.00  10.18  150.0  13.0  36.0  2937.0  161.0  55.7  0.0   \n",
       "590  17  女    3.45  10.18  152.0  15.0  35.0  2592.0  165.0  48.6  0.0   \n",
       "591  17  女    4.01   9.67  165.0  10.0  41.0  1829.0  154.0  43.6  0.0   \n",
       "592  17  女    4.48   9.09  180.0  10.0  46.0  2962.0  162.0  55.3  0.0   \n",
       "\n",
       "     女800米跑分数  女50米跑分数  \n",
       "0         100       72  \n",
       "1          40       10  \n",
       "2          80        0  \n",
       "3          85       70  \n",
       "4          85       68  \n",
       "..        ...      ...  \n",
       "588        78       70  \n",
       "589        76       64  \n",
       "590        80       64  \n",
       "591        74       68  \n",
       "592        50       74  \n",
       "\n",
       "[593 rows x 13 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#输入成绩，返回分数\n",
    "cols = [\"女800米跑\",\"女50米跑\"]\n",
    "for col in cols:\n",
    "    def getScore(result):\n",
    "        if result == 0: #未参加测试\n",
    "            return 0\n",
    "        for i in range(score_rule[col].shape[0]):#遍历评分表中的成绩\n",
    "            if result <= score_rule[col][\"成绩\"][i]:\n",
    "                return score_rule[col][\"分数\"][i]\n",
    "        return 0\n",
    "    score_female[col + \"分数\"] = score_female[col].map(getScore)\n",
    "score_female\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "####  非跑步类"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>女800米跑分数</th>\n",
       "      <th>女50米跑分数</th>\n",
       "      <th>女跳远分数</th>\n",
       "      <th>女体前屈分数</th>\n",
       "      <th>女仰卧分数</th>\n",
       "      <th>女肺活量分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>9.32</td>\n",
       "      <td>185.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100</td>\n",
       "      <td>72</td>\n",
       "      <td>85</td>\n",
       "      <td>76</td>\n",
       "      <td>85</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>11.44</td>\n",
       "      <td>148.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40</td>\n",
       "      <td>10</td>\n",
       "      <td>60</td>\n",
       "      <td>66</td>\n",
       "      <td>66</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>13.40</td>\n",
       "      <td>150.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>60</td>\n",
       "      <td>64</td>\n",
       "      <td>76</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>9.52</td>\n",
       "      <td>172.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>85</td>\n",
       "      <td>70</td>\n",
       "      <td>76</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>9.79</td>\n",
       "      <td>145.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>85</td>\n",
       "      <td>68</td>\n",
       "      <td>50</td>\n",
       "      <td>64</td>\n",
       "      <td>70</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>588</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.51</td>\n",
       "      <td>9.60</td>\n",
       "      <td>150.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>2255.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>78</td>\n",
       "      <td>70</td>\n",
       "      <td>60</td>\n",
       "      <td>95</td>\n",
       "      <td>78</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>589</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.00</td>\n",
       "      <td>10.18</td>\n",
       "      <td>150.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>2937.0</td>\n",
       "      <td>161.0</td>\n",
       "      <td>55.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>76</td>\n",
       "      <td>64</td>\n",
       "      <td>60</td>\n",
       "      <td>72</td>\n",
       "      <td>72</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.45</td>\n",
       "      <td>10.18</td>\n",
       "      <td>152.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>2592.0</td>\n",
       "      <td>165.0</td>\n",
       "      <td>48.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>80</td>\n",
       "      <td>64</td>\n",
       "      <td>62</td>\n",
       "      <td>76</td>\n",
       "      <td>72</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>591</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.01</td>\n",
       "      <td>9.67</td>\n",
       "      <td>165.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>1829.0</td>\n",
       "      <td>154.0</td>\n",
       "      <td>43.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>74</td>\n",
       "      <td>68</td>\n",
       "      <td>70</td>\n",
       "      <td>68</td>\n",
       "      <td>78</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>592</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.48</td>\n",
       "      <td>9.09</td>\n",
       "      <td>180.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>2962.0</td>\n",
       "      <td>162.0</td>\n",
       "      <td>55.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50</td>\n",
       "      <td>74</td>\n",
       "      <td>80</td>\n",
       "      <td>68</td>\n",
       "      <td>85</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>593 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  女800米跑  女50米跑    女跳远  女体前屈   女仰卧    女肺活量     身高    体重  BMI  \\\n",
       "0     1  女    3.22   9.32  185.0  16.0  48.0  3775.0  163.0  51.3  0.0   \n",
       "1     1  女    4.59  11.44  148.0   9.0  29.0  3683.0  163.0  66.6  0.0   \n",
       "2     1  女    3.46  13.40  150.0   7.0  40.0  3331.0  157.0  60.0  0.0   \n",
       "3     1  女    3.39   9.52  172.0  21.0  46.0  3701.0  160.0  50.7  0.0   \n",
       "4     1  女    3.43   9.79  145.0   8.0  34.0  3592.0  167.0  63.9  0.0   \n",
       "..   .. ..     ...    ...    ...   ...   ...     ...    ...   ...  ...   \n",
       "588  17  女    3.51   9.60  150.0  24.0  41.0  2255.0  158.0  49.0  0.0   \n",
       "589  17  女    4.00  10.18  150.0  13.0  36.0  2937.0  161.0  55.7  0.0   \n",
       "590  17  女    3.45  10.18  152.0  15.0  35.0  2592.0  165.0  48.6  0.0   \n",
       "591  17  女    4.01   9.67  165.0  10.0  41.0  1829.0  154.0  43.6  0.0   \n",
       "592  17  女    4.48   9.09  180.0  10.0  46.0  2962.0  162.0  55.3  0.0   \n",
       "\n",
       "     女800米跑分数  女50米跑分数  女跳远分数  女体前屈分数  女仰卧分数  女肺活量分数  \n",
       "0         100       72     85      76     85     100  \n",
       "1          40       10     60      66     66     100  \n",
       "2          80        0     60      64     76     100  \n",
       "3          85       70     76      90     85     100  \n",
       "4          85       68     50      64     70     100  \n",
       "..        ...      ...    ...     ...    ...     ...  \n",
       "588        78       70     60      95     78      70  \n",
       "589        76       64     60      72     72      85  \n",
       "590        80       64     62      76     72      76  \n",
       "591        74       68     70      68     78      60  \n",
       "592        50       74     80      68     85      85  \n",
       "\n",
       "[593 rows x 17 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#输入成绩，返回分数\n",
    "cols = [\"女跳远\",\"女体前屈\",\"女仰卧\",\"女肺活量\"]\n",
    "for col in cols:\n",
    "    def getScore(result):\n",
    "        if result == 0: #未参加测试\n",
    "            return 0\n",
    "        for i in range(score_rule[col].shape[0]):#遍历评分表中的成绩\n",
    "            if result >= score_rule[col][\"成绩\"][i]:\n",
    "                return score_rule[col][\"分数\"][i]\n",
    "        return 0\n",
    "    score_female[col + \"分数\"] = score_female[col].map(getScore)\n",
    "score_female"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "####  计算BMI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>女800米跑分数</th>\n",
       "      <th>女50米跑分数</th>\n",
       "      <th>女跳远分数</th>\n",
       "      <th>女体前屈分数</th>\n",
       "      <th>女仰卧分数</th>\n",
       "      <th>女肺活量分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>9.32</td>\n",
       "      <td>185.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>19.31</td>\n",
       "      <td>100</td>\n",
       "      <td>72</td>\n",
       "      <td>85</td>\n",
       "      <td>76</td>\n",
       "      <td>85</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>11.44</td>\n",
       "      <td>148.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>25.07</td>\n",
       "      <td>40</td>\n",
       "      <td>10</td>\n",
       "      <td>60</td>\n",
       "      <td>66</td>\n",
       "      <td>66</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>13.40</td>\n",
       "      <td>150.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>24.34</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>60</td>\n",
       "      <td>64</td>\n",
       "      <td>76</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>9.52</td>\n",
       "      <td>172.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>19.80</td>\n",
       "      <td>85</td>\n",
       "      <td>70</td>\n",
       "      <td>76</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>9.79</td>\n",
       "      <td>145.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>22.91</td>\n",
       "      <td>85</td>\n",
       "      <td>68</td>\n",
       "      <td>50</td>\n",
       "      <td>64</td>\n",
       "      <td>70</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>588</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.51</td>\n",
       "      <td>9.60</td>\n",
       "      <td>150.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>2255.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>19.63</td>\n",
       "      <td>78</td>\n",
       "      <td>70</td>\n",
       "      <td>60</td>\n",
       "      <td>95</td>\n",
       "      <td>78</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>589</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.00</td>\n",
       "      <td>10.18</td>\n",
       "      <td>150.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>2937.0</td>\n",
       "      <td>161.0</td>\n",
       "      <td>55.7</td>\n",
       "      <td>21.49</td>\n",
       "      <td>76</td>\n",
       "      <td>64</td>\n",
       "      <td>60</td>\n",
       "      <td>72</td>\n",
       "      <td>72</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.45</td>\n",
       "      <td>10.18</td>\n",
       "      <td>152.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>2592.0</td>\n",
       "      <td>165.0</td>\n",
       "      <td>48.6</td>\n",
       "      <td>17.85</td>\n",
       "      <td>80</td>\n",
       "      <td>64</td>\n",
       "      <td>62</td>\n",
       "      <td>76</td>\n",
       "      <td>72</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>591</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.01</td>\n",
       "      <td>9.67</td>\n",
       "      <td>165.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>1829.0</td>\n",
       "      <td>154.0</td>\n",
       "      <td>43.6</td>\n",
       "      <td>18.38</td>\n",
       "      <td>74</td>\n",
       "      <td>68</td>\n",
       "      <td>70</td>\n",
       "      <td>68</td>\n",
       "      <td>78</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>592</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.48</td>\n",
       "      <td>9.09</td>\n",
       "      <td>180.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>2962.0</td>\n",
       "      <td>162.0</td>\n",
       "      <td>55.3</td>\n",
       "      <td>21.07</td>\n",
       "      <td>50</td>\n",
       "      <td>74</td>\n",
       "      <td>80</td>\n",
       "      <td>68</td>\n",
       "      <td>85</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>593 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  女800米跑  女50米跑    女跳远  女体前屈   女仰卧    女肺活量     身高    体重    BMI  \\\n",
       "0     1  女    3.22   9.32  185.0  16.0  48.0  3775.0  163.0  51.3  19.31   \n",
       "1     1  女    4.59  11.44  148.0   9.0  29.0  3683.0  163.0  66.6  25.07   \n",
       "2     1  女    3.46  13.40  150.0   7.0  40.0  3331.0  157.0  60.0  24.34   \n",
       "3     1  女    3.39   9.52  172.0  21.0  46.0  3701.0  160.0  50.7  19.80   \n",
       "4     1  女    3.43   9.79  145.0   8.0  34.0  3592.0  167.0  63.9  22.91   \n",
       "..   .. ..     ...    ...    ...   ...   ...     ...    ...   ...    ...   \n",
       "588  17  女    3.51   9.60  150.0  24.0  41.0  2255.0  158.0  49.0  19.63   \n",
       "589  17  女    4.00  10.18  150.0  13.0  36.0  2937.0  161.0  55.7  21.49   \n",
       "590  17  女    3.45  10.18  152.0  15.0  35.0  2592.0  165.0  48.6  17.85   \n",
       "591  17  女    4.01   9.67  165.0  10.0  41.0  1829.0  154.0  43.6  18.38   \n",
       "592  17  女    4.48   9.09  180.0  10.0  46.0  2962.0  162.0  55.3  21.07   \n",
       "\n",
       "     女800米跑分数  女50米跑分数  女跳远分数  女体前屈分数  女仰卧分数  女肺活量分数  \n",
       "0         100       72     85      76     85     100  \n",
       "1          40       10     60      66     66     100  \n",
       "2          80        0     60      64     76     100  \n",
       "3          85       70     76      90     85     100  \n",
       "4          85       68     50      64     70     100  \n",
       "..        ...      ...    ...     ...    ...     ...  \n",
       "588        78       70     60      95     78      70  \n",
       "589        76       64     60      72     72      85  \n",
       "590        80       64     62      76     72      76  \n",
       "591        74       68     70      68     78      60  \n",
       "592        50       74     80      68     85      85  \n",
       "\n",
       "[593 rows x 17 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "score_female[\"BMI\"] = (score_female[\"体重\"]/(score_female[\"身高\"]/100)/(score_female[\"身高\"]/100)).round(2)\n",
    "score_female"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "####  重排列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女800米跑分数</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女50米跑分数</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女跳远分数</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女体前屈分数</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女仰卧分数</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>女肺活量分数</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>100</td>\n",
       "      <td>9.32</td>\n",
       "      <td>72</td>\n",
       "      <td>185.0</td>\n",
       "      <td>85</td>\n",
       "      <td>16.0</td>\n",
       "      <td>76</td>\n",
       "      <td>48.0</td>\n",
       "      <td>85</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>100</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>19.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>40</td>\n",
       "      <td>11.44</td>\n",
       "      <td>10</td>\n",
       "      <td>148.0</td>\n",
       "      <td>60</td>\n",
       "      <td>9.0</td>\n",
       "      <td>66</td>\n",
       "      <td>29.0</td>\n",
       "      <td>66</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>100</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>25.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>80</td>\n",
       "      <td>13.40</td>\n",
       "      <td>0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60</td>\n",
       "      <td>7.0</td>\n",
       "      <td>64</td>\n",
       "      <td>40.0</td>\n",
       "      <td>76</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>100</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>24.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>85</td>\n",
       "      <td>9.52</td>\n",
       "      <td>70</td>\n",
       "      <td>172.0</td>\n",
       "      <td>76</td>\n",
       "      <td>21.0</td>\n",
       "      <td>90</td>\n",
       "      <td>46.0</td>\n",
       "      <td>85</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>100</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>19.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>85</td>\n",
       "      <td>9.79</td>\n",
       "      <td>68</td>\n",
       "      <td>145.0</td>\n",
       "      <td>50</td>\n",
       "      <td>8.0</td>\n",
       "      <td>64</td>\n",
       "      <td>34.0</td>\n",
       "      <td>70</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>100</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>22.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>588</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.51</td>\n",
       "      <td>78</td>\n",
       "      <td>9.60</td>\n",
       "      <td>70</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60</td>\n",
       "      <td>24.0</td>\n",
       "      <td>95</td>\n",
       "      <td>41.0</td>\n",
       "      <td>78</td>\n",
       "      <td>2255.0</td>\n",
       "      <td>70</td>\n",
       "      <td>158.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>19.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>589</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.00</td>\n",
       "      <td>76</td>\n",
       "      <td>10.18</td>\n",
       "      <td>64</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60</td>\n",
       "      <td>13.0</td>\n",
       "      <td>72</td>\n",
       "      <td>36.0</td>\n",
       "      <td>72</td>\n",
       "      <td>2937.0</td>\n",
       "      <td>85</td>\n",
       "      <td>161.0</td>\n",
       "      <td>55.7</td>\n",
       "      <td>21.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.45</td>\n",
       "      <td>80</td>\n",
       "      <td>10.18</td>\n",
       "      <td>64</td>\n",
       "      <td>152.0</td>\n",
       "      <td>62</td>\n",
       "      <td>15.0</td>\n",
       "      <td>76</td>\n",
       "      <td>35.0</td>\n",
       "      <td>72</td>\n",
       "      <td>2592.0</td>\n",
       "      <td>76</td>\n",
       "      <td>165.0</td>\n",
       "      <td>48.6</td>\n",
       "      <td>17.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>591</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.01</td>\n",
       "      <td>74</td>\n",
       "      <td>9.67</td>\n",
       "      <td>68</td>\n",
       "      <td>165.0</td>\n",
       "      <td>70</td>\n",
       "      <td>10.0</td>\n",
       "      <td>68</td>\n",
       "      <td>41.0</td>\n",
       "      <td>78</td>\n",
       "      <td>1829.0</td>\n",
       "      <td>60</td>\n",
       "      <td>154.0</td>\n",
       "      <td>43.6</td>\n",
       "      <td>18.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>592</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.48</td>\n",
       "      <td>50</td>\n",
       "      <td>9.09</td>\n",
       "      <td>74</td>\n",
       "      <td>180.0</td>\n",
       "      <td>80</td>\n",
       "      <td>10.0</td>\n",
       "      <td>68</td>\n",
       "      <td>46.0</td>\n",
       "      <td>85</td>\n",
       "      <td>2962.0</td>\n",
       "      <td>85</td>\n",
       "      <td>162.0</td>\n",
       "      <td>55.3</td>\n",
       "      <td>21.07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>593 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  女800米跑  女800米跑分数  女50米跑  女50米跑分数    女跳远  女跳远分数  女体前屈  女体前屈分数  \\\n",
       "0     1  女    3.22       100   9.32       72  185.0     85  16.0      76   \n",
       "1     1  女    4.59        40  11.44       10  148.0     60   9.0      66   \n",
       "2     1  女    3.46        80  13.40        0  150.0     60   7.0      64   \n",
       "3     1  女    3.39        85   9.52       70  172.0     76  21.0      90   \n",
       "4     1  女    3.43        85   9.79       68  145.0     50   8.0      64   \n",
       "..   .. ..     ...       ...    ...      ...    ...    ...   ...     ...   \n",
       "588  17  女    3.51        78   9.60       70  150.0     60  24.0      95   \n",
       "589  17  女    4.00        76  10.18       64  150.0     60  13.0      72   \n",
       "590  17  女    3.45        80  10.18       64  152.0     62  15.0      76   \n",
       "591  17  女    4.01        74   9.67       68  165.0     70  10.0      68   \n",
       "592  17  女    4.48        50   9.09       74  180.0     80  10.0      68   \n",
       "\n",
       "      女仰卧  女仰卧分数    女肺活量  女肺活量分数     身高    体重    BMI  \n",
       "0    48.0     85  3775.0     100  163.0  51.3  19.31  \n",
       "1    29.0     66  3683.0     100  163.0  66.6  25.07  \n",
       "2    40.0     76  3331.0     100  157.0  60.0  24.34  \n",
       "3    46.0     85  3701.0     100  160.0  50.7  19.80  \n",
       "4    34.0     70  3592.0     100  167.0  63.9  22.91  \n",
       "..    ...    ...     ...     ...    ...   ...    ...  \n",
       "588  41.0     78  2255.0      70  158.0  49.0  19.63  \n",
       "589  36.0     72  2937.0      85  161.0  55.7  21.49  \n",
       "590  35.0     72  2592.0      76  165.0  48.6  17.85  \n",
       "591  41.0     78  1829.0      60  154.0  43.6  18.38  \n",
       "592  46.0     85  2962.0      85  162.0  55.3  21.07  \n",
       "\n",
       "[593 rows x 17 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = ['班级', '性别', '女800米跑','女800米跑分数',  '女50米跑','女50米跑分数', \n",
    "        '女跳远','女跳远分数', '女体前屈','女体前屈分数', '女仰卧','女仰卧分数', \n",
    "        '女肺活量', '女肺活量分数','身高',\n",
    "       '体重', 'BMI']\n",
    "score_female[cols]"
   ]
  }
 ],
 "metadata": {
  "hide_input": false,
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.0"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
